﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using Model;
using System.Data.SqlClient;
using System.Configuration;
using Dapper;
using System.Data;


namespace DAL
{
    public class CustomerDAL
    {
        public static readonly string SqlConnString = ConfigurationManager.ConnectionStrings["ELCO_ConnectionString"].ConnectionString;
        /// <summary>
        /// 新增客户信息
        /// </summary>
        /// <param name="CustomDiYu"></param>
        /// <param name="CustomName"></param>
        /// <param name="CustomChejian"></param>
        /// <param name="provience"></param>
        /// <param name="city"></param>
        /// <param name="area"></param>
        /// <param name="CustomDetailAddress"></param>
        /// <param name="CustomLng"></param>
        /// <param name="CustomLat"></param>
        /// <param name="CustomImg"></param>
        /// <param name="customerstate"></param>
        /// <param name="InstallDate"></param>
        /// <param name="installstate"></param>
        /// <param name="Reamrk"></param>
        /// <returns></returns>
        public static string addCustomInfo(string CustomDiYu, string CustomName, string CustomChejian, string provience, string city, string area, string CustomDetailAddress, string CustomLng, string CustomLat, string CustomImg,string customerstate,string InstallDate,string installstate, string Reamrk)
        {
            using (var conn = new SqlConnection(SqlConnString))
            {
                SqlCommand cmd = new SqlCommand();
                SqlTransaction transaction = null;
                try
                {
                    conn.Open();
                    transaction = conn.BeginTransaction();
                    cmd.Transaction = transaction;
                    cmd.Connection = conn;
                    cmd.CommandType = System.Data.CommandType.StoredProcedure;
                    SqlParameter[] sqlPara = new SqlParameter[15];

                    sqlPara[0] = new SqlParameter("@CustomDiYu", CustomDiYu);
                    sqlPara[0].Direction = System.Data.ParameterDirection.Input;
                    sqlPara[1] = new SqlParameter("@CustomName", CustomName);
                    sqlPara[1].Direction = System.Data.ParameterDirection.Input;
                    sqlPara[2] = new SqlParameter("@CustomChejian", CustomChejian);
                    sqlPara[2].Direction = System.Data.ParameterDirection.Input;
                    sqlPara[3] = new SqlParameter("@provience", provience);
                    sqlPara[3].Direction = System.Data.ParameterDirection.Input;
                    sqlPara[4] = new SqlParameter("@city", city);
                    sqlPara[4].Direction = System.Data.ParameterDirection.Input;
                    sqlPara[5] = new SqlParameter("@area", area);
                    sqlPara[5].Direction = System.Data.ParameterDirection.Input;
                    sqlPara[6] = new SqlParameter("@CustomDetailAddress", CustomDetailAddress);
                    sqlPara[6].Direction = System.Data.ParameterDirection.Input;
                    sqlPara[7] = new SqlParameter("@CustomLng", CustomLng);
                    sqlPara[7].Direction = System.Data.ParameterDirection.Input;
                    sqlPara[8] = new SqlParameter("@CustomLat", CustomLat);
                    sqlPara[8].Direction = System.Data.ParameterDirection.Input;
                    sqlPara[9] = new SqlParameter("@CustomImg", CustomImg);
                    sqlPara[9].Direction = System.Data.ParameterDirection.Input;
                    sqlPara[10] = new SqlParameter("@customerstate", customerstate);
                    sqlPara[10].Direction = System.Data.ParameterDirection.Input;
                    sqlPara[11] = new SqlParameter("@InstallDate", InstallDate);
                    sqlPara[11].Direction = System.Data.ParameterDirection.Input;
                    sqlPara[12] = new SqlParameter("@installstate", installstate);
                    sqlPara[12].Direction = System.Data.ParameterDirection.Input;
                    sqlPara[13] = new SqlParameter("@Reamrk", Reamrk);
                    sqlPara[13].Direction = System.Data.ParameterDirection.Input;
                    sqlPara[14] = new SqlParameter("@CatchFlag", 0);
                    sqlPara[14].Size = 10;
                    sqlPara[14].Direction = System.Data.ParameterDirection.Output;

                    foreach (SqlParameter para in sqlPara)
                    {
                        cmd.Parameters.Add(para);
                    }
                    cmd.CommandText = "[usp_Customer_AddCustomerInfo]";
                    cmd.ExecuteNonQuery();
                    transaction.Commit();
                    if (int.Parse(sqlPara[14].Value.ToString()) == 1)
                    {
                        return "hasexit";
                    }
                    return "success";
                }
                catch (Exception ex)
                {
                    transaction.Rollback();
                    return "falut";
                }
            }

        }

        /// <summary>
        /// 保存编辑客户信息
        /// </summary>
        /// <param name="CustomerID"></param>
        /// <param name="CustomDiYu"></param>
        /// <param name="CustomName"></param>
        /// <param name="CustomChejian"></param>
        /// <param name="provience"></param>
        /// <param name="city"></param>
        /// <param name="area"></param>
        /// <param name="CustomDetailAddress"></param>
        /// <param name="CustomLng"></param>
        /// <param name="CustomLat"></param>
        /// <param name="CustomImg"></param>
        /// <param name="customerstate"></param>
        /// <param name="InstallDate"></param>
        /// <param name="installstate"></param>
        /// <param name="Reamrk"></param>
        /// <returns></returns>
        public static string editCustomInfo(string CustomerID,string CustomDiYu, string CustomName, string CustomChejian, string provience, string city, string area, string CustomDetailAddress, string CustomLng, string CustomLat, string CustomImg, string customerstate, string InstallDate, string installstate, string Reamrk)
        {
            using (var conn = new SqlConnection(SqlConnString))
            {
                SqlCommand cmd = new SqlCommand();
                SqlTransaction transaction = null;
                try
                {
                    conn.Open();
                    transaction = conn.BeginTransaction();
                    cmd.Transaction = transaction;
                    cmd.Connection = conn;
                    cmd.CommandType = System.Data.CommandType.StoredProcedure;
                    SqlParameter[] sqlPara = new SqlParameter[16];
                    sqlPara[0] = new SqlParameter("@CustomerID", CustomerID);
                    sqlPara[0].Direction = System.Data.ParameterDirection.Input;

                    sqlPara[1] = new SqlParameter("@CustomDiYu", CustomDiYu);
                    sqlPara[1].Direction = System.Data.ParameterDirection.Input;

                    sqlPara[2] = new SqlParameter("@CustomName", CustomName);
                    sqlPara[2].Direction = System.Data.ParameterDirection.Input;

                    sqlPara[3] = new SqlParameter("@CustomChejian", CustomChejian);
                    sqlPara[3].Direction = System.Data.ParameterDirection.Input;

                    sqlPara[4] = new SqlParameter("@provience", provience);
                    sqlPara[4].Direction = System.Data.ParameterDirection.Input;

                    sqlPara[5] = new SqlParameter("@city", city);
                    sqlPara[5].Direction = System.Data.ParameterDirection.Input;

                    sqlPara[6] = new SqlParameter("@area", area);
                    sqlPara[6].Direction = System.Data.ParameterDirection.Input;

                    sqlPara[7] = new SqlParameter("@CustomDetailAddress", CustomDetailAddress);
                    sqlPara[7].Direction = System.Data.ParameterDirection.Input;

                    sqlPara[8] = new SqlParameter("@CustomLng", CustomLng);
                    sqlPara[8].Direction = System.Data.ParameterDirection.Input;

                    sqlPara[9] = new SqlParameter("@CustomLat", CustomLat);
                    sqlPara[9].Direction = System.Data.ParameterDirection.Input;

                    sqlPara[10] = new SqlParameter("@CustomImg", CustomImg);
                    sqlPara[10].Direction = System.Data.ParameterDirection.Input;

                    sqlPara[11] = new SqlParameter("@customerstate", customerstate);
                    sqlPara[11].Direction = System.Data.ParameterDirection.Input;

                    sqlPara[12] = new SqlParameter("@InstallDate", InstallDate);
                    sqlPara[12].Direction = System.Data.ParameterDirection.Input;

                    sqlPara[13] = new SqlParameter("@installstate", installstate);
                    sqlPara[13].Direction = System.Data.ParameterDirection.Input;

                    sqlPara[14] = new SqlParameter("@Reamrk", Reamrk);
                    sqlPara[14].Direction = System.Data.ParameterDirection.Input;

                    sqlPara[15] = new SqlParameter("@CatchFlag", 0);
                    sqlPara[15].Size = 10;
                    sqlPara[15].Direction = System.Data.ParameterDirection.Output;

                    foreach (SqlParameter para in sqlPara)
                    {
                        cmd.Parameters.Add(para);
                    }
                    cmd.CommandText = "[usp_Customer_EditCustomerInfo]";
                    cmd.ExecuteNonQuery();
                    transaction.Commit();
                    if (int.Parse(sqlPara[15].Value.ToString()) == 1)
                    {
                        return "hasexit";
                    }
                    return "success";
                }
                catch (Exception ex)
                {
                    transaction.Rollback();
                    return "falut";
                }
            }

        }

        /// <summary>
        /// 客户信息列表
        /// </summary>
        /// <param name="sortname"></param>
        /// <param name="sortorder"></param>
        /// <param name="keyvalue"></param>
        /// <returns></returns>
        public static DataTable GetCustomerInfo(string sortname, string sortorder, string keyvalue)
        {
            string str = "";
            DataTable dt = new DataTable();
            using (var conn = new SqlConnection(SqlConnString))
            {
                SqlCommand cmd = new SqlCommand();
                conn.Open();
                cmd.Connection = conn;

                str = "select ID,CustomDiYu,CustomName,CustomChejian,CustomProvience,CustomCity,CustomArea,CustomDetailAddress,CustomeState,InstallDate, case when (GETDATE()>InstallDate and UseDate is null) then '1' else InstallState end as InstallState,UseDate from CustomInfo where 1=1 ";
                if (keyvalue != "")
                {
                    str += " and CustomName like '%" + keyvalue + "%'";
                }
                str += "order by " + sortname + " " + sortorder + "";
                cmd.CommandType = CommandType.Text;
                cmd.CommandText = str;
                SqlDataAdapter Datapter = new SqlDataAdapter(cmd);
                Datapter.Fill(dt);
                return dt;
                
            }

        }

        /// <summary>
        /// 客户所购设备列表
        /// </summary>
        /// <param name="sortname"></param>
        /// <param name="sortorder"></param>
        /// <param name="CustomerID"></param>
        /// <param name="keyvalue"></param>
        /// <returns></returns>
        public static DataTable GetCustomerEquInfo(string sortname, string sortorder, string CustomerID, string keyvalue)
        {
            string str = "";
            DataTable dt = new DataTable();
            using (var conn = new SqlConnection(SqlConnString))
            {
                SqlCommand cmd = new SqlCommand();
                conn.Open();
                cmd.Connection = conn;

                str = "select a.ID,b.CustomName,a.EquType,a.totalcount,a.EquNo,a.TaiHao from CustomerEquInfo a left join CustomInfo b on a.CustomerID=b.ID where CustomerID='" + CustomerID + "' ";
                if (keyvalue != "")
                {
                    str += " and a.EquNo like '%" + keyvalue + "%'";
                }
                str += "order by " + sortname + " " + sortorder + "";
                cmd.CommandType = CommandType.Text;
                cmd.CommandText = str;
                SqlDataAdapter Datapter = new SqlDataAdapter(cmd);
                Datapter.Fill(dt);
                return dt;
            }

        }

        /// <summary>
        ///  根据ID查找客户厂名   车间
        /// </summary>
        /// <param name="ID"></param>
        /// <returns></returns>
        public static List<CustomerNameChejian> getCustomNameByID(string ID)
        {
            using (var conn = new SqlConnection(SqlConnString))
            {

                string str = "select ID, CustomName,CustomChejian from CustomInfo where ID='" + ID + "' ";
                return conn.Query<CustomerNameChejian>(str).ToList();
            }

        }
        /// <summary>
        /// 根据ID查找客户全部信息
        /// </summary>
        /// <param name="ID"></param>
        /// <returns></returns>
        public static List<CustomerInfo> getCustomInfoByID(string ID)
        {
            using (var conn = new SqlConnection(SqlConnString))
            {

                string str = "select * from CustomInfo where ID='" + ID + "' ";
                return conn.Query<CustomerInfo>(str).ToList();
            }

        }

        public static string saveCustomerEquKaoHaoInfo(string CustomerID, string EquType, string totalcount, List<string> EquNo, List<string> TaiHao)
        {
            using (var conn = new SqlConnection(SqlConnString))
            {
                SqlCommand cmd = new SqlCommand();
                SqlTransaction transaction = null;
                try
                {
                    conn.Open();
                    transaction = conn.BeginTransaction();
                    cmd.Transaction = transaction;
                    cmd.Connection = conn;
                    cmd.CommandType = System.Data.CommandType.StoredProcedure;
                    SqlParameter[] sqlPara = new SqlParameter[5];
                    //string limitno = string.Join(",", EquNo.ToArray());
                    for (int i = 0; i < EquNo.Count; i++)
                    {
                        if (i == 0)
                        {
                            sqlPara[0] = new SqlParameter("@CustomerID", CustomerID);
                            sqlPara[0].Direction = System.Data.ParameterDirection.Input;
                            sqlPara[1] = new SqlParameter("@EquType", EquType);
                            sqlPara[1].Direction = System.Data.ParameterDirection.Input;
                            sqlPara[2] = new SqlParameter("@totalcount", totalcount);
                            sqlPara[2].Direction = System.Data.ParameterDirection.Input;
                            sqlPara[3] = new SqlParameter("@EquNo ", EquNo[i]);
                            sqlPara[3].Direction = System.Data.ParameterDirection.Input;
                            sqlPara[4] = new SqlParameter("@TaiHao ", TaiHao[i]);
                            sqlPara[4].Direction = System.Data.ParameterDirection.Input;

                            foreach (SqlParameter para in sqlPara)
                            {
                                cmd.Parameters.Add(para);
                            }
                        }
                        else
                        {
                            cmd.Parameters[3].Value = EquNo[i];
                            cmd.Parameters[4].Value = TaiHao[i];
                        }
                        cmd.CommandText = "[usp_Customer_saveCustomerEquKaoHaoInfo]";
                        cmd.ExecuteNonQuery();
                    }
               
                    
                    transaction.Commit();
                    //if (int.Parse(sqlPara[4].Value.ToString()) == 1)
                    //{
                      //  return "hasexit";
                    //}
                    return "success";
                }
                catch (Exception ex)
                {
                    transaction.Rollback();
                    return "falut";
                }
            }

        }

        /// <summary>
        /// 删除客户信息
        /// </summary>
        /// <param name="UserID"></param>
        /// <returns></returns>
        public static int deleteCustomerInfo(string CustomerID)
        {
            using (var conn = new SqlConnection(SqlConnString))
            {
                ////开始事务
                //IDbTransaction transaction = conn.BeginTransaction();
                //try
                //{
                //    string query = "DELETE FROM  CustomInfo WHERE ID = @CustomerID";
                //    string query2 = "DELETE FROM  CustomerEquInfo WHERE CustomerID = @CustomerID";
                //    conn.Execute(query2, new { CustomerID = CustomerID }, transaction, null, null);
                //    conn.Execute(query, new { CustomerID = CustomerID }, transaction, null, null);
                //    //提交事务
                //    transaction.Commit();

                //    return 1;
                //}
                //catch (Exception ex)
                //{
                //    //出现异常，事务Rollback
                //    transaction.Rollback();
                //    return 0;
                //}
                int affecgtRow = 0;
                string sql = "DELETE FROM  CustomInfo WHERE ID = @CustomerID";
                affecgtRow = conn.Execute(sql, new { CustomerID = CustomerID });
                if (affecgtRow == 1)
                {
                    string query2 = "DELETE FROM  CustomerEquInfo WHERE CustomerID = @CustomerID";
                    conn.Execute(query2, new { CustomerID = CustomerID });
                }
                return affecgtRow;
            }
        }

        /// <summary>
        /// 删除客户所购设备
        /// </summary>
        /// <param name="CustomerEquID"></param>
        /// <returns></returns>
        public static int deleteCustomerEquInfo(string CustomerEquID)
        {
            int affecgtRow = 0;

            using (var conn = new SqlConnection(SqlConnString))
            {
                string sql = "DELETE FROM  CustomerEquInfo WHERE ID = @CustomerEquID;";
                affecgtRow = conn.Execute(sql, new { CustomerEquID = CustomerEquID });
            }
            return affecgtRow;
        }

        /// <summary>
        /// 设置客户设备投入使用时间
        /// </summary>
        /// <param name="CustomerID"></param>
        /// <param name="UseDate"></param>
        /// <returns></returns>
        public static string setCustomerUseDate(string CustomerID, string UseDate)
        {
            using (var conn = new SqlConnection(SqlConnString))
            {
                SqlCommand cmd = new SqlCommand();
                SqlTransaction transaction = null;
                try
                {
                    conn.Open();
                    transaction = conn.BeginTransaction();
                    cmd.Transaction = transaction;
                    cmd.Connection = conn;
                    cmd.CommandType = System.Data.CommandType.StoredProcedure;
                    SqlParameter[] sqlPara = new SqlParameter[2];

                    sqlPara[0] = new SqlParameter("@CustomerID", CustomerID);
                    sqlPara[0].Direction = System.Data.ParameterDirection.Input;
                    sqlPara[1] = new SqlParameter("@UseDate", UseDate);
                    sqlPara[1].Direction = System.Data.ParameterDirection.Input;
                   
                    foreach (SqlParameter para in sqlPara)
                    {
                        cmd.Parameters.Add(para);
                    }

                    cmd.CommandText = "[usp_Customer_setCustomerUseDate]";
                    cmd.ExecuteNonQuery();
                    transaction.Commit();
                    return "success";
                }
                catch (Exception ex)
                {
                    transaction.Rollback();
                    return "falut";
                }
            }

        }


        /// <summary>
        /// 监听客户需要维护的设备信息
        /// </summary>
        /// <param name="CustomerID"></param>
        /// <returns></returns>
        public static List<EquInfo> getCustomerMaintenanceInfo(string CustomerID)
        {
            using (var conn = new SqlConnection(SqlConnString))
            {

                string str = "select EquType,TaiHao from CustomerEquInfo customerequ left join CustomInfo customer on customerequ.CustomerID = customer.ID where DATEDIFF(DAY,case when MaintenanceDate is null then UseDate else MaintenanceDate end,getdate())>=MaintenanceCycle+3 and customerequ.CustomerID = @CustomerID;";
                return conn.Query<EquInfo>(str, new { CustomerID = CustomerID }).ToList();
            }

        }

        public static string getCustomerProblem(string UserNo)
        {
            using (SqlConnection connection = new SqlConnection(SqlConnString))
            {
                string sql = "select top(1) ID from ProblemMessage where ToUserNo = '" + UserNo + "' and RepeateState = 0 order by MessageTime desc ";
                return connection.Query<string>(sql, null, null, true, null, null).FirstOrDefault<string>();
            }
        }

    }
}
